##
##  Database.py -- routines to work with sqlite3 and to build databases
##
""" do these show up as module comments ?
    if they do, then it is good
"""
from collections import namedtuple
import sqlite3

## ##########################################################################

database_name = './db/DBRW_2014.sqlite3'

def OpenMe() :
    """ usage: (con,cur) = OpenMe()
    """
    con = sqlite3.connect(database_name, detect_types=sqlite3.PARSE_DECLTYPES)
    cur = con.cursor()
    return (con,cur)

def CloseMe(con,cur) :
    cur.close()
    con.close()



## ##########################################################################



## to define a database table AND to create namedtuples
## use a list of these records
FieldDecl = namedtuple( 'FieldDecl', 'field_name, field_type' )
    # field_name --string-- is used on both the sqlite3 table AND as a field in the named tuple
    # field_type --string-- must be one of the keys in the transformer_dictionary


## these are field types that sqlite already understands
default_transformer_dictionary = {
    'TEXT' : None ,
    'REAL' : None
    #DOES NOT WORK AS EXPECTED 'timestamp' : None  # uses built in type conversion -- we really don't need to use that ...
    }
   # the key name must be one of
   # 1) an sqlite type name -- ex. REAL TEXT ...
   # 2) a builtin python/sqlite3 adaptor/converter type -- ex. timestamp
   # 3) some other arbitraty type string representing a python class
   #    in practice, using the class name seems to work
   # the value attached to the key must None, or a triple of
   #    ( class, adpater_function, converter_function )
   # NOTE: the adaptor/converter string will be a byte string
   # in the adapter, do this: ret = str(rtl).encode('ascii')
   # in the converter, do sanity checks using byte strings


## this is used for building a named tuple
## caller must pass in a list of field list records
## this function returns the field_name_list
def make_namedtuple( name,fdl ) :
    """ Given name and fdl -- a list of FieldDecl records
        return a namedtuple type
        ex: MyTuple = Database.make_namedtuple('MyTuple', fdl)
    """
    fl = ''
    c = ''
    for (n,_) in fdl :
        fl += c + n
        c = ' '
    return namedtuple(name,fl)


## attach transformers
def attach_transformers( fdl, atd ) :
    """ Given fdl -- a FieldDecl list
        and an atd -- an additional transformer dictionary
        this function will register converters/adaptors with sqlite3
        this function also works to double check the programmers work
    """
    #print ("Database.attach_transformers",fdl,atd)
    for (_,t) in fdl :
        # define sac and make sure programmer setup the tables correctly
        if t in default_transformer_dictionary :
            assert ( t not in atd ) # don't define twice !
            sac = default_transformer_dictionary[t]
        elif t in atd :
            sac = atd[ t ]
            assert ( sac is not None ) # why did you pass this in ?
        else :
            assert(False) # unknown field type !!
        # if defined, register the adaptor/converters
        if sac is None :
            continue
        (s,a,c) = sac # (class,adapter,converter)
        #print( "registering adapter", s, a )
        sqlite3.register_adapter( s, a) # python to sqlite3
        #print( "registering converter", t, c )
        sqlite3.register_converter( t, c ) # sqlite3 to python



## ##########################################################################
## ##########################################################################
## ##########################################################################



## assure_table_exists and create it if it doesnt
def assure_table_exists(table_name, fdl) :
    """ Given a table_name and a FieldDecl list fdl
        open the main database and try to create the named table with given fdl columns
        if the table already exists in exactly the same form -- nothing happens
        if the table exists in a different form -- an error results
        otherwise, the table is created
    """

    (con,cur) = OpenMe()

    # build the sqlite3 create command
    ex = "CREATE TABLE IF NOT EXISTS " + table_name + "(" # will be the list of name type pairs
    c = ''
    for (n,t) in fdl : #(name,type)
        ex += c + n + " " + t
        c=','
    ex += ")"

    #print("Database.assure_table_exists: ", ex)

    cur.execute( ex ) # Create table
        # if the table already exists in a different form, just barf
        # user needs to NOT overwrite anything important
    con.commit()# Save (commit) the changes

    CloseMe(con,cur)


## this is just for dev ...
def dump_table(table_name) :
    """ don't do this at home
        for that matter, go use the sqlite built in dump
    """

    (con,cur) = OpenMe()

    ex = "SELECT * FROM {}".format(table_name)
    print(ex)
    cur.execute( ex ) # Create table
    re = []
    while re is not None :
        re = cur.fetchone()
        print("   fetched:", re)

    CloseMe(con,cur)




## assure that the given record exists
## if it does not, then it creates it
def assure_record_exists( table_name, fdl, *field_values ) :

    (con,cur) = OpenMe()

    # see <http://stackoverflow.com/questions/13880786/python-sqlite3-string-variable-in-execute>
    ## we have to hard code the column names, and 'softcode' the value names

    ex = "SELECT * FROM {} WHERE".format(table_name)
    c = ' '
    for fd in fdl :
        ex += "{}{}==?".format(c,fd.field_name)
        c = ' AND '
    #print(ex,field_values)
    # now query using field_values for the ?
    cur.execute(ex,field_values)

#    r = cur.fetchone()
#
#    #print("fetched: ",r)
#    if r is None :
#        ex = "INSERT INTO {} VALUES (".format(table_name)
#        c = ''
#        for v in field_values :
#            ex += c+"?"
#            c = ','
#        ex += ")"
#        #print(ex,field_values)
#        cur.execute(ex,field_values)
#
#        con.commit()# Save (commit) the changes
#
#    CloseMe(con,cur)

    r = cur.fetchone()
    CloseMe(con,cur)

    #print("fetched: ",r)
    if r is None :
        return store_to_table( table_name, field_values )



def store_to_table( table_name, field_values ) :

    (con,cur) = OpenMe()

    # see <http://stackoverflow.com/questions/13880786/python-sqlite3-string-variable-in-execute>
    ## we have to hard code the column names, and 'softcode' the value names

    ex = "INSERT INTO {} VALUES (".format(table_name)
    c = ''
    for v in field_values :
        ex += c+"?"
        c = ','
    ex += ")"

    #print(ex,field_values)

    cur.execute(ex,field_values)

    con.commit()# Save (commit) the changes

    CloseMe(con,cur)



def get_recent_timestamp( table_name,fdl, *filter ) :
    """ returns the row with last time stamp not exceeding timestamp
        if additional fields are given, they are used as == filters
        filter must have the time value
        the first entry in fdl must be timestamp ...
    """

    assert( fdl[0].field_name == 'timestamp' )
    assert( len(filter) >= 1 )

    (con,cur) = OpenMe()

    ex = "SELECT * FROM {} WHERE".format(table_name)
    c=''
    op='<'
    for (fd,_) in zip(fdl,filter) : # allow filter to be shorter than fdl
        ex += " {} {} {} ?".format( c, fd.field_name, op )
        c = 'AND'
        op = '=='
    ex+= " ORDER by {} DESC".format( fdl[0].field_name ) # may need to add ASC or DESC
    ex+= " LIMIT 1"

    #print(ex,filter)

    cur.execute(ex,filter)

    #ret=None
    #re = []
    #while re is not None :
    #    re = cur.fetchone()
    #    print("  fetched:",re)
    #    if ret is None : ret = re
    ret = cur.fetchone()

    CloseMe(con,cur)

    return ret
